package com.bw.dao;

import com.bw.bean.Sugar1;
import com.bw.bean.Sugar2;
import com.bw.bean.Sugar3;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Select;

import java.util.List;

@Mapper
public interface SugarDao {
    @Select("select hour(create_time) `name`,count(*) `value` from user_chapter_process GROUP BY hour(create_time)")
    List<Sugar1> getList1();

    @Select("select tp.paper_title `name`,\n" +
            "count(te.paper_id) `num`,\n" +
            "sum(te.score)/count(te.paper_id) `avgScore`,\n" +
            "sum(te.duration_sec)/count(te.paper_id) `avgTime`\n" +
            "from test_exam te \n" +
            "LEFT JOIN test_paper tp ON te.paper_id=tp.id \n" +
            "GROUP BY tp.paper_title\n")
    List<Sugar2> getList2();

    @Select("select \n" +
            "(select count(*) from payment_info where date_format(create_time,'%Y-%m-%d')='2021-04-23')\n" +
            "-\n" +
            "(select count(*) from payment_info where date_format(create_time,'%Y-%m-%d')='2021-04-22') `value`")
    double getList3();

    @Select("select  \n" +
            "ci.course_name 'name',\n" +
            "a.avgScore,\n" +
            "a.num1,\n" +
            "b.num2 ,\n" +
            "b.num2/a.num1 lv \n" +
            "from\n" +
            "(select  course_id,avg(review_stars) avgScore,count(*) num1 from review_info group by  course_id) a,\n" +
            "(select  course_id,count(*) num2  from review_info where review_stars>=3  group by  course_id) b,\n" +
            "course_info ci\n" +
            "where a.course_id=b.course_id and a.course_id=ci.id\n")
    List<Sugar3> getList4();

    @Select("select hour(create_time) `name`,count(*) `value` from user_chapter_process GROUP BY hour(create_time)")
    List<Sugar1> getList5();

    @Select("select tp.paper_title `name`,\n" +
            "count(*) num,\n" +
            "avg(score) avgScore,\n" +
            "avg(duration_sec) avgTime\n" +
            "from test_exam te \n" +
            "LEFT JOIN test_paper tp ON te.paper_id=tp.id \n" +
            "GROUP BY tp.paper_title\n")
    List<Sugar2> getList6();

    @Select("select tp.paper_title `name`,\n" +
            "count(*) `num`,\n" +
            "sum(te.score)/count(*) `avgScore`,\n" +
            "sum(te.duration_sec)/count(*) `avgTime`\n" +
            "from test_exam te \n" +
            "LEFT JOIN test_paper tp ON te.paper_id=tp.id\n" +
            "GROUP BY tp.paper_title")
    List<Sugar2> getList7();

    @Select("select bs.source_site `name`,count(oi.user_id) `value` from order_info oi \n" +
            "LEFT JOIN order_detail od ON oi.id=od.order_id \n" +
            "LEFT JOIN video_info vi ON od.course_id=vi.course_id\n" +
            "LEFT JOIN base_source bs ON bs.id=vi.video_source_id\n" +
            "where bs.source_site is not null\n" +
            "GROUP BY bs.source_site")
    List<Sugar1> getList8();
}
